package itsm.isperp.module.service.incident;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.domain.PageResponse;
import itsm.isperp.framework.domain.IUser;
import itsm.isperp.framework.service.BaseService;
import itsm.isperp.framework.utils.HibernateUUIDGenerator;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.module.dto.project.ProjectDomain;
import itsm.isperp.module.dto.project.ProjectDomain.ProjectDomainType;
import itsm.isperp.module.dto.solr.SolrIcdIncidentDTO;
import itsm.isperp.module.entity.app.AppUser;
import itsm.isperp.module.entity.incident.IcdCategory;
import itsm.isperp.module.entity.incident.IcdIncident;
import itsm.isperp.module.entity.incident.IcdIncidentHandleProcess;
import itsm.isperp.module.entity.incident.IcdIncidentProperty;
import itsm.isperp.module.repository.incident.IcdIncidentMapper;
import itsm.isperp.module.repository.solr.SolrIcdIncidentRepository;
import itsm.isperp.module.service.app.AppDictService;
import itsm.isperp.workflow.service.BpmProcessRunService;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.solr.core.query.Criteria;
import org.springframework.data.solr.core.query.HighlightQuery;
import org.springframework.data.solr.core.query.SimpleHighlightQuery;
import org.springframework.data.solr.core.query.SimpleStringCriteria;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * 事件管理服务
 * 
 * @author lizx
 * @date 2013-4-11
 * @version V1.0
 */
@Service
public class IcdIncidentService extends BaseService<IcdIncident> {

  public static final Logger logger = LoggerFactory
      .getLogger(IcdIncidentService.class);

  @Autowired
  protected IcdIncidentMapper icdIncidentMapper;

  @Autowired
  protected SolrIcdIncidentRepository solrIcdIncidentRepository;

  @Autowired
  protected IcdIncidentHandleProcessService icdIncidentHandleProcessService;

  @Autowired
  protected AppDictService appDictService;

  @Autowired
  protected JdbcTemplate jdbcTemplate;

  @Override
  public IcdIncidentMapper getRepository() {
    return this.icdIncidentMapper;
  }

  // save 用此方法
  @Override
  public <S extends IcdIncident> int saveOrUpdateAndSpecific(S entity,
      HttpServletRequest req, String... otherFields) {

    Enumeration<String> params = req.getParameterNames();
    Set<String> includeColumns = new HashSet<String>();
    String kid = entity.getId();
    String categoryId = entity.getIcdCategoryId();
    // IcdIncident model=this.findOne(entity.getId());

    for (; params.hasMoreElements();) {
      String v = params.nextElement();
      if (v.charAt(0) == '_') {
        v = v.substring(1);
      }
      includeColumns.add(v);
    }

    if (otherFields != null) {
      for (String s : otherFields) {
        if (!includeColumns.contains(s))
          includeColumns.add(s);
      }
    }

    // 保存扩展属性 edit at 20160906
    Map<String, String[]> pa = req.getParameterMap();


    if (pa.containsKey("tid_incidentProperty")) {
      IcdIncidentPropertyService icdIncidentPropertyService = ContextHolder
          .getSpringBean("icdIncidentPropertyService");
      Set<String> set = pa.keySet();
      // 插入到扩展表
      String sqlInsert = "";
      String sqlUpdate = "";
      String sqlInsertColumns = "";
      String sqlInsertValues = "";
      boolean ifUpdate = false;
      boolean ifInsert = false;
      String sql = "select * from icd_category_property_setting where category_id='"
          + categoryId + "' and (db_name is not null and db_name<>'')";
      List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
      if (list.size() > 0) {
        String dbName = list.get(0).get("db_name").toString();
        String sql0 = "select count(1) from " + dbName + " where relation_id='"
            + kid + "'";
        Integer ifcount = jdbcTemplate.queryForObject(sql0, Integer.class);
        if (ifcount > 0) {
          sqlUpdate = "update " + dbName + " set ";
        } else {
          sqlInsert = "insert into " + dbName + "";
        }
      }

      for (String k : set) {
        if (k.indexOf("c_") > -1) {
          IcdIncidentProperty ip = new IcdIncidentProperty();
          String kv = "";
          String[] proValue = pa.get(k);
          for (String s1 : proValue) {
            kv += s1 + ",";
          }
          String tk = k.replace("c_", "");
          kv = kv.substring(0, kv.length() - 1);
          String tId = kid + tk;
          ip.setId(tId);
          ip.setRelationId(kid);
          ip.setCategoryPropertyId(tk);
          ip.setValue(kv);
          if (StringUtils.isNotEmpty(kv)) {
            icdIncidentPropertyService.saveOrUpdate(ip);
            if (StringUtils.isNotBlank(sqlInsert)) {
              sqlInsertColumns += tk + ",";
              sqlInsertValues += "'" + kv + "',";
              ifInsert = true;
            } else if (StringUtils.isNotBlank(sqlUpdate)) {
              sqlUpdate += tk + "='" + kv + "',";
              ifUpdate = true;
            }
          }

        }
      }
      if (ifUpdate) {
        sqlUpdate = sqlUpdate.substring(0, sqlUpdate.length() - 1);
        sqlUpdate += " where relation_id='" + kid + "'";
        jdbcTemplate.execute(sqlUpdate);
      } else if (ifInsert) {
        sqlInsertColumns = sqlInsertColumns.substring(0,
            sqlInsertColumns.length() - 1);
        sqlInsertValues = sqlInsertValues.substring(0,
            sqlInsertValues.length() - 1);
        sqlInsert += "(relation_id," + sqlInsertColumns + ") values('" + kid
            + "'," + sqlInsertValues + ")";
        jdbcTemplate.execute(sqlInsert);
      }
    }

    if (StringUtils.isEmpty(kid)) {
      entity.setId(HibernateUUIDGenerator.generate());
      // 新建事件时默认登记时间以及当前登记者以及初始状态
      entity.setCreateDate(new Timestamp(new java.util.Date().getTime()));
      entity.setCreator(ContextHolder.getLoginUsername());
      entity.setCreatorName(ContextHolder.getLoginUser().getFullname());
      entity.setStatus(this.appDictService.getInitStatus("INCIDENT_STATUS"));

      return this.getRepository().save(entity);
    } else {
      if (this.getRepository().existsByClass(entity.getId(), entity.getClass()) == null) {
        // 新建事件时默认登记时间以及当前登记者以及初始状态
        entity.setCreateDate(new Timestamp(new java.util.Date().getTime()));
        entity.setCreator(ContextHolder.getLoginUsername());
        entity.setCreatorName(ContextHolder.getLoginUser().getFullname());
        entity.setStatus(this.appDictService.getInitStatus("INCIDENT_STATUS"));
        return this.getRepository().save(entity);
      } else {
        this.getRepository().dynamicSpecificUpdate(entity, includeColumns);

        if (includeColumns.contains("solution")) {

          IcdIncidentHandleProcess e = new IcdIncidentHandleProcess();
          e.setRelationId(kid);

          IUser user = ContextHolder.getLoginUser();
          e.setHandlerId(user.getUsername());
          e.setHandlerName(user.getFullname());
          e.setHandleProcess(entity.getHandleProcess());
          e.setReasonAnalyze(entity.getReasonAnalyze());
          e.setSolution(entity.getSolution());
          icdIncidentHandleProcessService.saveOrUpdate(e);
        }
      }
      return 1;
    }
  }

  /**
   * 查询类似事件(根据参数获取前三天内类似事件---统一资产或同一个报障人的标题相似的事件<标题需支持全文检索>)
   * 
   * @param ciId
   *          资产标识
   * @param cusUserName
   *          报障人标识
   * @param icdCategoryName
   *          事件分类
   * @param title
   *          事件标题
   * @return 事件分页列表
   */
  public Page<SolrIcdIncidentDTO> searchSimilarIncidents(String ciId,
      String cusUserName, String icdCategoryName, String title,
      Pageable pageable) {

    HighlightQuery query = new SimpleHighlightQuery();
    SimpleStringCriteria c = new SimpleStringCriteria("title:" + title);

    if (StringUtils.isNotEmpty(ciId)) {
      c.or(new Criteria("ciId").contains(ciId));
    }
    if (StringUtils.isNotEmpty(cusUserName)) {
      c.or(new Criteria("cusUserName").contains(cusUserName));
    }
    if (StringUtils.isNotEmpty(icdCategoryName)) {
      c.or(new Criteria("icdCategoryName").contains(icdCategoryName));
    }
    query.addCriteria(c);
    query.setPageRequest(pageable);

    return solrIcdIncidentRepository.searchSimilarIncident(query);
  }

  /**
   * 获取电子病历，此方法需支持全文检索
   * 
   * @param ciId
   * @param content
   *          搜索维护信息
   * @return
   */
  public Page<SolrIcdIncidentDTO> searchEMR(String ciId, String content) {

    HighlightQuery query = new SimpleHighlightQuery();
    SimpleStringCriteria c = new SimpleStringCriteria("title:" + content);

    if (StringUtils.isNotEmpty(ciId)) {
      c.and(new Criteria("ciId").is(ciId));
    }
    return solrIcdIncidentRepository.searchSimilarIncident(query);

  }

  /**
   * 根据配置项ID获取故障履历
   * 
   * @param ciId
   *          配置项ID
   * @return
   */
  public List<IcdIncident> findCiIncident(String ciId) {
    JqGridRequest req = new JqGridRequest();
    req.addFilterRule("ciId", JqGridFilterRuleOp.eq, ciId); //
    req.addSidx("createDate DESC");
    return icdIncidentMapper.findAllByRequest(req).getContent();

  }

  /**
   * 根据配置项ID获取故障履历
   * 
   * @param ciId
   *          配置项ID
   * @return
   */
  public List<Map<String, Object>> findCiIncidentDetail(String ciId) {
    /*
     * JqGridRequest req = new JqGridRequest(); req.setPageable(false);
     * req.addFilterRule("ci_id", JqGridFilterRuleOp.eq, ciId); //
     * req.addSidx("create_date DESC"); return
     * icdIncidentMapper.findAllDetailByRequest(req).getContent();
     */
    String sql = "SELECT a.* FROM  icd_incident a WHERE EXISTS (SELECT 1 FROM conf_ci_use_relation b WHERE b.relation_id=a.id AND b.relation_type='incidentProcess' AND ci_id='"
        + ciId + "') order by create_date desc";
    return jdbcTemplate.queryForList(sql);
  }

  public PageResponse<Map<String, Object>> searchAllByFilter(
      JqGridRequest request) {
    request.setProjectDomain(ProjectDomain.newInstance("project_id",
        ProjectDomainType.Technology));
    return icdIncidentMapper.findAllDetailByRequest(request).toPage();
  }

  public PageResponse<Map<String, Object>> findIncidentWithPropertyMapByRequest(
      String categoryId, JqGridRequest request) {
    String tableName = "";
    String sql = "SELECT db_name FROM icd_category_property_setting where id='"
        + categoryId + "'";
    String dbName = jdbcTemplate.queryForObject(sql, String.class);
    return icdIncidentMapper.findIncidentWithPropertyMapByRequest(dbName,
        request).toPage();
  }

  public PageResponse<Map<String, Object>> findIncidentTotalByRequest(
      JqGridRequest request) {
    request.setProjectDomain(ProjectDomain.newInstance("project_id",
        ProjectDomainType.Technology));
    return icdIncidentMapper.findIncidentTotalByRequest(request).toPage();
  }

  public List<Map<String, Object>> getUserIncidentGroupBySLAStatus() {
    JqGridRequest req = new JqGridRequest();
    req.isPageable(false);

    req.setSelectColumns(
        "CASE sla_status WHEN 1 THEN '异常' ELSE '正常' END  sla_status ",
        "COUNT(1) AS result_count");

    req.setSearchString("close_code IS NULL   ");
    req.addFilterRule("process_id", JqGridFilterRuleOp.ne, "");

    req.setProjectDomain(ProjectDomain.newInstance("project_id",
        ProjectDomainType.Technology));

    req.setGroupBy("sla_status");

    return this.icdIncidentMapper.findAllDetailByRequest(req);

  }

  /**
   * 获取用于在项目与中事件级别的统计
   * 
   * @return
   */
  public List<Map<String, Object>> getUserIncidentGroupByPriority() {
    return getUserNoCloseIncidentGroupBy("priority_name", "priority_name",
        "INCIDENT_PRIORITY");
  }

  public List<Map<String, Object>> getUserIncidentGroupByProject() {
    return getUserNoCloseIncidentGroupBy("project_name", "project_name", null);
  }

  public List<Map<String, Object>> getUserIncidentGroupByCategory() {
    String sql = "SELECT cname,COUNT(1) AS result_count FROM (SELECT fn_splitString(b.treenode_name_path,'/',2) AS cname FROM `icd_incident` a INNER JOIN `icd_category` b ON a.icd_category_id=b.id where a.close_code is null)t GROUP BY cname";
    return jdbcTemplate.queryForList(sql);

  }

  public List<Map<String, Object>> getUserIncidentGroupByStatus() {

    return getUserNoCloseIncidentGroupBy("status ", "status", null);

  }

  private List<Map<String, Object>> getUserNoCloseIncidentGroupBy(
      final String columnName, String groupBy, final String typeCode) {
    JqGridRequest req = new JqGridRequest();
    req.isPageable(false);

    // req.addFilterRule(columnName, JqGridFilterRuleOp.ne, "");

    req.addFilterRule("process_id", JqGridFilterRuleOp.ne, "");
    req.setSelectColumns(columnName, "COUNT(1) AS result_count");

    req.setSearchString("close_code IS NULL   ");

    req.setProjectDomain(ProjectDomain.newInstance("project_id",
        ProjectDomainType.Technology));

    req.setGroupBy(groupBy);

    return this.icdIncidentMapper.findAllDetailByRequest(req);

  }

  public Page<Map<String, Object>> findIncidentDetails(JqGridRequest request) {
    /*
     * request.setProjectDomain(ProjectDomain.newInstance("project_id",
     * ProjectDomainType.Technology));
     */

    return this.icdIncidentMapper.findAllDetailByRequest(request).toPage();

  }

  public Page<IcdIncident> findClosedIncidentByCusUserId(String id,
      String cusUserId, JqGridRequest request) {
    request.addFilterRule("id", JqGridFilterRuleOp.ne, id);
    request.addFilterRule("cusUserId", JqGridFilterRuleOp.eq, cusUserId);
    request.setSearchString("closed_date IS NOT NULL");
    request.setSort("create_date", Direction.DESC);
    request.setSelectColumns("id", "code", "title", "create_date",
        "description", "status", "solver_name", "solved_date");

    return this.icdIncidentMapper.findAllByRequest(request).toPage();

  }

  public Page<IcdIncident> findAllIncidentByCusUserId(String id,
      String cusUserId, JqGridRequest request) {
    request.addFilterRule("cusUserId", JqGridFilterRuleOp.eq, cusUserId);
    request.setSort("create_date", Direction.DESC);
    request.setSelectColumns("id", "code", "title", "create_date",
        "description", "status", "solver_name", "solved_date");
    return this.icdIncidentMapper.findAllByRequest(request).toPage();

  }

  /**
   * 根据ciId获取到关联路径相同的事件分类
   * 
   * @param ciId
   * @return
   */
  public IcdCategory getCategoryByCiId(String ciId) {
    return this.getRepository().findCategoryByCiId(ciId);

  }



  /**
   * 解决人满意度统计
   * 
   * @return
   */
  public List<Map<String, Object>> findSolverSatisfaction() {
    return this.icdIncidentMapper.findSolverSatisfaction();
  }

  public Map<String, Object> findCusUserIncidentDetail(String id) {
    /*
     * JqGridRequest req = new JqGridRequest(); req.setPageable(false);
     * req.addFilterRule("ci_id", JqGridFilterRuleOp.eq, ciId); //
     * req.addSidx("create_date DESC"); return
     * icdIncidentMapper.findAllDetailByRequest(req).getContent();
     */
    String sql = "SELECT a.*,b.name cus_name,b.fullname cus_fullname,b.email cus_email,b.mobile cus_mobile,'' cus_mobile_short,b.tel cus_tel,b.user_type cus_type,b.department_treenode_name_path cus_dep,b.position cus_position FROM ICD_INCIDENT a LEFT JOIN v_app_department_user b ON a.cus_user_id=b.id"
        + " where a.id='" + id + "'";
    return jdbcTemplate.queryForMap(sql);
  }

  public String doUpdateicdTime(String code, String createTime,
      String responseTime, String arriveTime, String solveTime, String closeTime) {

    String sql1 = "select * from icd_incident where code like '%" + code + "%'";
    List<Map<String, Object>> list = jdbcTemplate.queryForList(sql1);

    int length = list.size();
    for (int i = 0; i < length; i++) {
      String pro_id = (String) list.get(i).get("process_id");
      String id = (String) list.get(i).get("id");

      List<String> sqllist = new ArrayList<>();
      // 更新开始节点
      String sql2 = "update bpm_action_history set action_time='" + createTime
          + "' where action_name='提交' and process_id='" + pro_id
          + "' and action_flow_id is null";
      // 更新工程师受理节点
      String sql3 = "update bpm_action_history set action_time='"
          + responseTime + "' where action_name='受理' and process_id='" + pro_id
          + "'";
      // 更新处理节点
      String sql31 = "update bpm_action_history set action_time='" + solveTime
          + "' where action_name='处理完成' and process_id='" + pro_id + "'";
      // 更新事件回访
      String sql4 = "update bpm_action_history set action_time='" + closeTime
          + "' where action_name='关闭事件' and process_id='" + pro_id + "'";

      String tempRep = "response_date=null";
      if (!responseTime.equals("")) {
        tempRep = "response_date='" + responseTime + "'";
      }

      String tempArri = "arrival_date=null";
      if (!arriveTime.equals("")) {
        tempArri = "arrival_date='" + arriveTime + "'";
      }

      String sql5 = "UPDATE icd_incident SET create_date='" + createTime
          + "',assign_date='" + createTime + "'," + tempRep + "," + tempArri
          + ",solved_date='" + solveTime + "',closed_date='" + closeTime
          + "' where id='" + id + "'";

      // 更新事件编号
      String sql6 = "";
      String newCode = code.substring(7, 10);
      SimpleDateFormat sFormat = new SimpleDateFormat("yyMM");

      sqllist.add(sql2);
      sqllist.add(sql3);
      sqllist.add(sql4);
      sqllist.add(sql5);

      String[] sql = new String[sqllist.size()];
      sql = sqllist.toArray(sql);
      jdbcTemplate.batchUpdate(sqllist.toArray(sql));
    }

    return "";
  }
  
  public static void sendCpMessage(String id,String description)
  {
    String sql="select supporter_id from icd_incident where id='"+id+"'";
    JdbcTemplate jdbcTemplate1=ContextHolder.getSpringBean("jdbcTemplate");
    List<Map<String,Object>> li=jdbcTemplate1.queryForList(sql);
    
    if(li.size()>0)
    {
      String spId=li.get(0).get("supporter_id").toString();
      String[] s=spId.split(",");
      for(String sp:s)
      {
        itsm.isperp.module.utils.WechatChatUtils.sendCpMessage(sp,description);
      }
    }
    
  }
  public static String plugUpdate(String id,String cus_user_id) {
   //更新deal_type
   //根据人员ID 获取项目
    
    String sql="SELECT a.id,GROUP_CONCAT(b.app_user_name) app_user_names,GROUP_CONCAT(b.member_name) member_names FROM proj_project a INNER JOIN proj_project_member b"
        +" ON a.id=b.proj_project_id where cus_customer_id=(select customer_id from app_user where id='"+cus_user_id+"') and project_type like '%驻场%'"
        +" GROUP BY b.proj_project_id";
    JdbcTemplate jdbcTemplate1=ContextHolder.getSpringBean("jdbcTemplate");
    String sql2_0="select department_treenode_path from v_app_department_user where id='"+cus_user_id+"'";
    String cusUserDeptTreeNode=jdbcTemplate1.queryForObject(sql2_0, String.class);
    List<Map<String,Object>> result=jdbcTemplate1.queryForList(sql);
    if(result!=null)
    { 
      if(result.size()==1)
      {
        String supporters=result.get(0).get("app_user_names").toString();
        String supporterNames=result.get(0).get("member_names").toString();
        String projectId=result.get(0).get("id").toString();
        String sql12="update icd_incident set deal_type=2,supporter_id='"+supporters+"',supporter_name='"+supporterNames+"',project_id='"+projectId+"' where id='"+id+"'";
        jdbcTemplate1.execute(sql12);
        return "2";
      }else if(result.size()>1)
      {
        
        String sql2="SELECT a.id,GROUP_CONCAT(b.app_user_name) app_user_names,GROUP_CONCAT(b.member_name) member_names FROM proj_project a INNER JOIN proj_project_member b"
            +" ON a.id=b.proj_project_id where a.id=(SELECT a1.id FROM proj_project a1 LEFT JOIN app_department  b1 ON a1.`cus_department_id`=b1.`id` WHERE INSTR('"+cusUserDeptTreeNode+"',cus_department_id)>0 ORDER BY b1.`treenode_name_path` DESC LIMIT 1) and project_type like '%驻场%'"
            +" GROUP BY b.proj_project_id limit 1";
        List<Map<String,Object>> result2=jdbcTemplate1.queryForList(sql2);
        if(result2.size()>0)
        {
          String supporters=result2.get(0).get("app_user_names").toString();
          String supporterNames=result2.get(0).get("member_names").toString();
          String projectId=result2.get(0).get("id").toString();
          String sql12="update icd_incident set deal_type=2,supporter_id='"+supporters+"',supporter_name='"+supporterNames+"',project_id='"+projectId+"' where id='"+id+"'";
          jdbcTemplate1.execute(sql12);
          return "2";
          
        }
      }else
      {
        return null;
      }
      
      
    }else 
    {
      String sql2="SELECT a.id,GROUP_CONCAT(b.app_user_name) app_user_names,GROUP_CONCAT(b.member_name) member_names FROM proj_project a INNER JOIN proj_project_member b"
          +" ON a.id=b.proj_project_id where a.id=(SELECT a1.id FROM proj_project a1 LEFT JOIN app_department  b1 ON a1.`cus_department_id`=b1.`id` WHERE INSTR('"+cusUserDeptTreeNode+"',cus_department_id)>0 ORDER BY b1.`treenode_name_path` DESC LIMIT 1) and project_type like '%常规%' AND b.position_id=(SELECT id FROM app_role WHERE NAME='运维主管')"
          +" GROUP BY b.proj_project_id";
      List<Map<String,Object>> result2=jdbcTemplate1.queryForList(sql2);
      if(result2!=null)
      {
        if(result2.size()==1)
        {
          String supporters=result2.get(0).get("app_user_names").toString();
          String supporterNames=result2.get(0).get("member_names").toString();
          String projectId=result2.get(0).get("id").toString();
          String sql12="update icd_incident set deal_type=2,supporter_id='"+supporters+"',supporter_name='"+supporterNames+"',project_id='"+projectId+"' where id='"+id+"'";
          jdbcTemplate1.execute(sql12);
          return "2";
        }else if(result2.size()>1)
        {
          String sql3="SELECT a.id,GROUP_CONCAT(b.app_user_name) app_user_names,GROUP_CONCAT(b.member_name) member_names FROM proj_project a INNER JOIN proj_project_member b"
              +" ON a.id=b.proj_project_id where cus_customer_id=(select customer_id from app_user where id='"+cus_user_id+"') and project_type like '%常规%' AND b.position_id=(SELECT id FROM app_role WHERE NAME='运维主管')"
              +" GROUP BY b.proj_project_id limit 1";
          List<Map<String,Object>> result3=jdbcTemplate1.queryForList(sql3);
          if(result3.size()>0)
          {
            String supporters=result3.get(0).get("app_user_names").toString();
            String supporterNames=result3.get(0).get("member_names").toString();
            String projectId=result3.get(0).get("id").toString();
            String sql12="update icd_incident set deal_type=2,supporter_id='"+supporters+"',supporter_name='"+supporterNames+"',project_id='"+projectId+"' where id='"+id+"'";
            jdbcTemplate1.execute(sql12);
            return "2";
            
          }
        }else
        {
          return null;
        }
      }else
      {
        return null;
      }
      
    }
    return null;
   
  }
  
  public  ResultMessage checkDelete(String id) {
    //更新deal_type
    //根据人员ID 获取项目
     String sql="select * from icd_incident where id='"+id+"'";
     List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
     if(list.size()>0)
     {
       Map<String,Object> rs=list.get(0);
       if(rs.get("process_id")==null || rs.get("code")==null || StringUtils.isEmpty(rs.get("code").toString()))
       {
         return ResultMessage.newInstance(true);
       }else
       {
         return ResultMessage.newInstance(false);
       }
       
     }else
     {
       return ResultMessage.newInstance(false);
     }
   }

  // 产生随机数
  public int getRandomNum(int beg, int end) {
    Random random = new Random(System.currentTimeMillis());
    int ret = random.nextInt(end - beg) + beg;
    return ret;
  }
  
  public Date dealDate(Date sourceDate)
  {
    Date result=sourceDate;
    int hour=result.getHours();
    if(hour>18 || hour<8)
    {
      Calendar cal = Calendar.getInstance();   
      cal.setTime(sourceDate);   
      cal.add(Calendar.HOUR, 6);// 24小时制   
      sourceDate = cal.getTime();
      result=dealDate(sourceDate);
    }
    return result;
  }
  
  public String doUpdateTime(String code,Date createTime)
  {
    String sql11="select * from icd_incident where code='"+code+"' limit 1";
    String process_id="";
    String id="";
    List<Map<String,Object>> list11=jdbcTemplate.queryForList(sql11);
    id=list11.get(0).get("id").toString();
    process_id=list11.get(0).get("process_id").toString();
    
    String sql0="SELECT * FROM bpm_action_history WHERE process_id='"+process_id+"' ORDER BY task_id";
    List<Map<String,Object>> list=jdbcTemplate.queryForList(sql0);
    Date distributeDate=createTime;
    Date responseDate=createTime;
    Date solvedDate=createTime;
    Date closeDate=createTime;
    long beginTime = createTime.getTime();
    Date expectDate=new Date(beginTime + getRandomNum(1, 100) * 1000 * 60);
    if(list.size()>0)
    {
      Date lastUpdate=createTime;
      
      for(int i=0;i<list.size();i++)
      { 
        String actionId=list.get(i).get("id").toString();
        if(i>0)
        {
          long distributeTime = lastUpdate.getTime() + getRandomNum(1, 300) * 1000 * 60;
          lastUpdate = new Date(distributeTime);
          lastUpdate=dealDate(lastUpdate);
         
        }
        String sql1="update bpm_action_history set action_time=? where id=?";
        jdbcTemplate.update(sql1,new Object[]{lastUpdate,actionId});
      }
      
      
      String sql3="SELECT action_time FROM bpm_action_history where action_name='接单' and process_id='"+process_id+"'";
      List<Map<String,Object>> list3=jdbcTemplate.queryForList(sql3);
      if(list3.size()>0)
      {
        Date d3=(Date)list3.get(0).get("action_time");
        responseDate=d3;
      }
      
      String sql4="SELECT action_time FROM bpm_action_history where action_name='处理完成' and process_id='"+process_id+"'";
      List<Map<String,Object>> list4=jdbcTemplate.queryForList(sql4);
      if(list4.size()>0)
      {
        Date d4=(Date)list4.get(0).get("action_time");
        solvedDate=d4;
      }
      
      String sql5="SELECT action_time FROM bpm_action_history where action_name='关闭事件' and process_id='"+process_id+"'";
      List<Map<String,Object>> list5=jdbcTemplate.queryForList(sql5);
      if(list5.size()>0)
      {
        Date d5=(Date)list5.get(0).get("action_time");
        closeDate=d5;
      }
    }else
    {
      long distributeTime = beginTime + getRandomNum(1, 10) * 1000 * 60;
     distributeDate = new Date(distributeTime);
      // 工程师受理时间
      long responseTime = distributeTime + getRandomNum(20, 50) * 1000 * 60;
      responseDate = new Date(responseTime);
      responseDate=dealDate(responseDate);
      // 工程师处理时间
      long solvedTime = responseTime + getRandomNum(30, 70) * 1000 * 60;
      solvedDate = new Date(solvedTime);
      solvedDate=dealDate(solvedDate);
      // 关闭时间
      long closeTime = solvedTime + getRandomNum(60, 360) * 1000 * 60;
      closeDate = new Date(closeTime);
      closeDate=dealDate(closeDate);
      
    }

 // 更新事件编号
    String tnewCode = code.substring(7, 11);
    SimpleDateFormat sFormat = new SimpleDateFormat("yyMM");
    String codeT = sFormat.format(createTime);
    String newCode = "YW-" + codeT + tnewCode;

    String sql6 = "UPDATE icd_incident SET create_date='"
        + createTime.toLocaleString() + "',assign_date='"
        + distributeDate.toLocaleString() + "',response_date='"
        + responseDate.toLocaleString() + "',solved_date='"
        + solvedDate.toLocaleString() + "',closed_date='"
        + closeDate.toLocaleString() + "',expect_time='"
        + expectDate.toLocaleString() + "',solver_submit_time='"
        + solvedDate.toLocaleString() + "',happen_time='"
        + createTime.toLocaleString() + "',code='" + newCode + "' where id='"
        + id + "'";
    jdbcTemplate.update(sql6);
    return "ok";
  }
  

  public String doUpdateicdTime2(String code, Date createTime, Date closeDateT) {
    String sql1 = "select * from icd_incident where code like '%" + code + "%'";
    List<Map<String, Object>> list = jdbcTemplate.queryForList(sql1);

    long beginTime = createTime.getTime();
    // 分派时间
    long distributeTime = beginTime + getRandomNum(1, 10) * 1000 * 60;
    Date distributeDate = new Date(distributeTime);
    // 工程师受理时间
    long responseTime = distributeTime + getRandomNum(20, 50) * 1000 * 60;
    Date responseDate = new Date(responseTime);
    // 工程师处理时间
    long solvedTime = responseTime + getRandomNum(30, 70) * 1000 * 60;
    Date solvedDate = new Date(solvedTime);
    // 关闭时间
    long closeTime = solvedTime + getRandomNum(60, 360) * 1000 * 60;
    Date closeDate = new Date(closeTime);
    if (closeDateT != null)
      closeDate = closeDateT;
    // 期望完成时间
    long expectTime = beginTime + 3600 * 1000 * 60;
    Date expectDate = new Date(expectTime);

    if (list.get(0).get("deal_type") != null
        && list.get(0).get("deal_type").equals("1")) {
      distributeDate = closeDate;
      responseDate = createTime;
      solvedDate = createTime;
      closeDate = createTime;
    }

    int length = list.size();
    for (int i = 0; i < length; i++) {
      String pro_id = (String) list.get(i).get("process_id");
      String id = (String) list.get(i).get("id");

      List<String> sqllist = new ArrayList<>();
      // 更新开始节点
      String sql2 = "update bpm_action_history set action_time='"
          + createTime.toLocaleString()
          + "' where action_name='提交' and process_id='" + pro_id
          + "' and action_flow_id is null";
      // 更新分派时间
      String sql21 = "update bpm_action_history set action_time='"
          + distributeDate.toLocaleString()
          + "' where action_name='提交' and process_id='" + pro_id
          + "' and action_flow_id is not null";
      // 更新工程师受理节点
      String sql3 = "update bpm_action_history set action_time='"
          + responseDate.toLocaleString()
          + "' where action_name='受理' and process_id='" + pro_id + "'";
      String sql32 = "update bpm_action_history set action_time='"
          + responseDate.toLocaleString()
          + "' where action_name='转派' and process_id='" + pro_id + "'";
      // 更新处理节点
      String sql31 = "update bpm_action_history set action_time='"
          + solvedDate.toLocaleString()
          + "' where action_name='处理完成' and process_id='" + pro_id + "'";
      // 更新事件回访
      String sql4 = "update bpm_action_history set action_time='"
          + closeDate.toLocaleString()
          + "' where action_name='关闭事件' and process_id='" + pro_id + "'";

      // 更新事件编号
      String tnewCode = code.substring(7, 11);
      SimpleDateFormat sFormat = new SimpleDateFormat("yyMM");
      String codeT = sFormat.format(createTime);
      String newCode = "YW-" + codeT + tnewCode;

      String sql5 = "UPDATE icd_incident SET create_date='"
          + createTime.toLocaleString() + "',assign_date='"
          + distributeDate.toLocaleString() + "',response_date='"
          + responseDate.toLocaleString() + "',solved_date='"
          + solvedDate.toLocaleString() + "',closed_date='"
          + closeDate.toLocaleString() + "',expect_time='"
          + expectDate.toLocaleString() + "',solver_submit_time='"
          + solvedDate.toLocaleString() + "',happen_time='"
          + createTime.toLocaleString() + "',code='" + newCode + "' where id='"
          + id + "'";

      sqllist.add(sql2);
      sqllist.add(sql21);
      sqllist.add(sql3);
      sqllist.add(sql31);
      sqllist.add(sql32);
      sqllist.add(sql4);
      sqllist.add(sql5);

      String[] sql = new String[sqllist.size()];
      sql = sqllist.toArray(sql);
      jdbcTemplate.batchUpdate(sqllist.toArray(sql));
    }

    return "ok";
  }
  
  public boolean submitNewIncident(String title,String description,String userMobile){
    IcdIncident model = new IcdIncident();
    AppUser user = (AppUser) ContextHolder.getLoginUser();
    String linkmanName = user.getFullname();
    String userTel = user.getTel();
    model.setLinkmanName(linkmanName);
    String userTel_ = StringUtils.isEmpty(userTel) ? "" : userTel + "/";
    model.setLinkmanPhone(userTel_ + userMobile);
    model.setSource("Web");
    model.setTitle(title);
    model.setDescription(description);
    model.setCusUserId(user.getId());
    this.saveOrUpdate(model);
    BpmProcessRunService bpmProcessRunService = ContextHolder
        .getSpringBean("bpmProcessRunService");
    try {
      bpmProcessRunService.startProcess(user.getUsername(), "incidentProcess",
          model.getId(), "");
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return true;
    
  }
  public void saveSatisfaction(IcdIncident entity) {

    entity.setAreFeedback(true);

    Set<String> columns = new HashSet<>();
    columns.add("satisfaction");
    columns.add("satisfaction_quality");
    columns.add("satisfaction_approach");
    columns.add("satisfaction_timely");
    columns.add("customerOpinion");
    columns.add("are_feedback");
    columns.add("satisfaction_date");
    columns.add("are_link");

    this.icdIncidentMapper.dynamicSpecificUpdate(entity, columns);

  }
  
  public void gotoNextStep(String tableName,String processKey,String nextTaskId,String nextTransitionId,String status,String handColumn)
  {
    String sql0="select * from "+tableName+" where 1=1 and "+status+"";
    List<Map<String,Object>> list=jdbcTemplate.queryForList(sql0);
    for(Map<String,Object> m:list)
    {
      String id=m.get("id").toString();
      String sup=m.get(handColumn).toString();
      String processId=m.get("process_id").toString();
      BpmProcessRunService bpmProcessRunService = ContextHolder
          .getSpringBean("bpmProcessRunService");
      try {
        bpmProcessRunService.gotoNextSetp(sup, processKey,
            nextTaskId, nextTransitionId, processId, id, null, "", 0);
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      
    }
  }
  
  public void startIcd()
  {
    String sql="select * from icd_incident where process_id is null ";
    List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
    for(Map<String,Object> m:list)
    {
      String id=m.get("id").toString();
      String fwt=m.get("confirmtimeman_id").toString();
      BpmProcessRunService bpmProcessRunService = ContextHolder
          .getSpringBean("bpmProcessRunService");
      /*String getRd="SELECT app_user_name FROM `v_app_user_role` WHERE NAME='服务台' AND app_user_name<>'admin' ORDER BY RAND() LIMIT 1";
      String fwt=jdbcTemplate.queryForObject(getRd, String.class);*/
      try {
        bpmProcessRunService.startProcess(fwt, "incidentProcess",
            id, "");
        
        
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      
    }
  }
  
  public void startProb()
  {
    String sql="select * from prob_problem where process_id is null ";
    List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
    for(Map<String,Object> m:list)
    {
      String id=m.get("id").toString();
      String fwt=m.get("creator").toString();
      BpmProcessRunService bpmProcessRunService = ContextHolder
          .getSpringBean("bpmProcessRunService");
      /*String getRd="SELECT app_user_name FROM `v_app_user_role` WHERE NAME='服务台' AND app_user_name<>'admin' ORDER BY RAND() LIMIT 1";
      String fwt=jdbcTemplate.queryForObject(getRd, String.class);*/
      try {
        bpmProcessRunService.startProcess(fwt, "problemProcess",
            id, "");
        
        
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      
    }
  }
  
  
}
